{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def clean_data(x):\n",
    "    for i in range(len(x)):\n",
    "        if re.match('[\\d]',str(x[i])) is not None:\n",
    "            x[i]=int(re.match(r'[\\d]',str(x[i])).group())\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def clean_datafile(f):\n",
    "    f=open(f)\n",
    "    dt=pd.read_csv(f)\n",
    "    dt.ix[:,25:]=dt.ix[:,25:].apply(clean_data,axis=1)\n",
    "    dt.replace('CAPSE',np.nan,inplace=True)\n",
    "    dt['航司']=list(map(lambda x:x[:2],dt['航班号']))\n",
    "    dt['何时首次收到航班变更的信息？'].replace({'A':5,'B':4,'C':3,'D':1},inplace=True)\n",
    "    dt['航司是否提供快速退改签服务？'].replace({'A':4,'B':2,'C':3},inplace=True)\n",
    "    dt['去往机场交通满意度']=dt[['机场大巴服务','停车场服务']].mean(1)\n",
    "    dt['到达机场交通满意度']=dt[['机场大巴满意度','出租车满意度','交通满意度（地铁、其他）','停车场满意度']].mean(1)\n",
    "    dt['值机满意度（机场）']=dt[['机场柜台值机满意度','机场自助值机满意度']].mean(1)\n",
    "    dt['值机满意度（航司）']=dt[['机场柜台值机满意度','机场自助值机满意度','手机短信值机满意度','航司官网值机满意度','航司APP值机满意度','飞常准值机满意度','其他渠道值机满意度']].mean(1)\n",
    "    dt['延误信息提醒']=dt[['延误信息提醒满意度','何时首次收到航班变更的信息？']].mean(1)\n",
    "    dt['退改签']=dt[['退改签服务满意度','航司是否提供快速退改签服务？']].mean(1)\n",
    "    dt['延误餐']=dt[['延误餐满意度','协助提供延误餐满意度']].mean(1)\n",
    "    dt['住宿']=dt[['住宿满意度','协助提供住宿满意度','休息场所安排满意度']].mean(1)\n",
    "    return dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def Airport_Compute(x):\n",
    "    NativeAirport=['PEK','PVG','CAN','CTU','SZX','KMG','SHA','XIY','CKG','HGH','NKG','XMN','CGO','CSX','TAO','WUH','HAK',\n",
    "                  'URC','TSN','SYX','HRB','KWE','DLC','SHE','TNA','NNG','LHW','FOC','TYN','CGQ','KHN','HET']\n",
    "    AirportIndex1=['出发地编码','目的地编码','总体满意度','机场大巴服务', '停车场服务','去往机场交通满意度','机场行李推车','工作人员服务',\n",
    "                  '机场指示牌','机场环境卫生','机场设施设备','机场信息通报','机场卫生间','机场WIFI',\n",
    "                  '机场建设文化特色','值机满意度（机场）','机场柜台值机满意度','机场自助值机满意度',\n",
    "                  '机场餐饮种类满意度','机场商品种类','机场餐饮价格','机场商品价格','安检满意度','延误信息提醒',\n",
    "                  '延误餐','住宿','地面不正常航班保障']\n",
    "    AirportIndex2=['出发地编码','目的地编码','总体满意度','评论','机场大巴满意度','出租车满意度','交通满意度（地铁、其他）',\n",
    "                  '停车场满意度','到达机场交通满意度','行李推车满意度','机场卫生间满意度','对行李保护','行李传输时间满意度（在行李转盘等待的时间）',\n",
    "                   '行李提取引导']\n",
    "    x1=x[AirportIndex1].groupby('出发地编码').mean().ix[NativeAirport]\n",
    "    x2=x[AirportIndex2].groupby('目的地编码').mean().ix[NativeAirport]\n",
    "    return x1.merge(x2,left_index=True,right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def Airline_Compute(x):\n",
    "    AirlineIndex=['航司','总体满意度','购票流程便捷性','购票服务规范性','空乘服务','机上广播','机上餐食','机上饮品','机上娱乐',\n",
    "              '客舱环境','座椅舒适度','机上卫生间','对行李保护','行李传输时间满意度（在行李转盘等待的时间）','出发机场摆渡车服务',\n",
    "              '到达机场摆渡车服务','登机满意度','值机满意度（航司）','延误餐','住宿','地面不正常航班保障','退改签','延误信息提醒',\n",
    "              '机上不正常航班保障']\n",
    "    NativeAirline=['3U','MF','TV','CZ','G5','FM','SC','ZH','CA','KY','MU','HU','GS','NS','HO','JD','GJ','DZ','EU','BK']\n",
    "    return x[AirlineIndex].groupby('航司').mean().ix[NativeAirline]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2821: DtypeWarning: Columns (2,30,83,96,97,98,104,120,121,124,125,126,127,131) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  if self.run_code(code, result):\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2821: DtypeWarning: Columns (2,28,42,58,60,83,91,98,100,104,106,120,121,125,126,127,129,130) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  if self.run_code(code, result):\n"
     ]
    }
   ],
   "source": [
    "f1=r'C:\\Users\\xy\\Desktop\\export_2018-06-01_2018-06-30_survey_9.csv'\n",
    "f2=r'C:\\Users\\xy\\Desktop\\export_2018-07-01_2018-07-31_survey_9.csv'\n",
    "dt1=clean_datafile(f1)\n",
    "dt2=clean_datafile(f2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Airport_Compute=Airport_Compute(dt2)-Airport_Compute(dt1)\n",
    "Airline_Compute=Airline_Compute(dt2)-Airline_Compute(dt1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(Airline_Compute)[['购票流程便捷性','购票服务规范性','空乘服务','机上广播','机上餐食','机上饮品','客舱环境','座椅舒适度',\n",
    "                               '机上卫生间','机上娱乐','延误餐','住宿','地面不正常航班保障','退改签','延误信息提醒','机上不正常航班保障',\n",
    "                               '对行李保护','行李传输时间满意度（在行李转盘等待的时间）','出发机场摆渡车服务','到达机场摆渡车服务','登机满意度',\n",
    "                              '值机满意度（航司）']].to_excel(r'C:\\Users\\xy\\Desktop\\Airline_Compute.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(Airport_Compute)[['机场大巴服务','停车场服务','机场大巴满意度','出租车满意度','交通满意度（地铁、其他）',\n",
    "                               '停车场满意度','去往机场交通满意度','到达机场交通满意度','机场行李推车','行李推车满意度',\n",
    "                               '工作人员服务','机场指示牌','机场环境卫生','机场设施设备','机场信息通报','机场卫生间',\n",
    "                               '机场卫生间满意度','机场WIFI','机场建设文化特色','值机满意度（机场）','机场柜台值机满意度',\n",
    "                               '机场自助值机满意度','机场餐饮种类满意度','机场商品种类','机场餐饮价格','机场商品价格',\n",
    "                               '安检满意度','对行李保护','行李传输时间满意度（在行李转盘等待的时间）','行李提取引导',\n",
    "                               '延误信息提醒','地面不正常航班保障','延误餐','住宿']].to_excel(r'C:\\Users\\xy\\Desktop\\Airport_Compute.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
